<?php
/**
 * Description of OQSDimClass
 *
 * Sub Open Question Single Code Dimension
 *
 * @author Chisheng Lu, Jeffy Shih
 * @copyright Coopyright(c)2010 DoRISE team, Science Education center, NTNU.
 * @since 2010-03-31
 * @version 1.0
 */
class OQSDim {
    //put your code here
    private $mysqli;

    function __construct() {
        //引入資料庫連結參數設定
        include '../inc/oaConn.inc.php';
        $this->mysqli = @new mysqli($mysqlHost,$mysqlUser,$mysqlPassword,$mysqlDB);
        if(mysqli_connect_errno()) {
            throw new Exception('無法建立資料庫連線！'.mysqli_connect_error());
            $this->mysqli = false;
            exit();
        }

        //連線使用編碼
        $this->mysqli->query("SET NAMES 'utf8'");
    }

    private function nextOQSDCode($intOQSId, $strDimCode) {

        $sql_selOQSD = 'SELECT Max(`OQSDim`.`OQSDCode`) FROM `OQSDim`
            INNER JOIN `OpenQSCode` ON `OQSDim`.`OQSId` = `OpenQSCode`.`OQSId`
            WHERE `OQSDim`.`DimCode` = \'' . $strDimCode . '\' AND `OpenQSCode`.`SQId` = (SELECT `SQId` FROM `OpenQSCode` WHERE `OQSId` = ' . $intOQSId . ')';
        $rs_selOQSD = $this->mysqli->query($sql_selOQSD);
        if (!$rs_selOQSD) {
            throw new Exception('查詢問答題單編碼向度的編碼錯誤！');
        } else {
            if ($rs_selOQSD->num_rows == 0) {
                return '01';
            } else {
                $row_getOQSD = $rs_selOQSD->fetch_row();
                if ($row_getOQSD[0] == '98') {
                    throw new Exception('查詢問答題單編碼向度層級的編碼時, 編碼已達最大值98！');
                } else {
                    return sprintf("%02d", ($row_getOQSD[0] + 1));
                }
            }
        }
    }

    // $intOQSId: OQSId of table OpenQSCode
    // Returns Boolean
    public function addOQSDim($intOQSId, $strDimCode) {
        $bolRtn = false;
        //檢查問答題單編碼編號是否為空白
        if (empty ($intOQSId)) {
            throw new Exception('缺少問答題單編碼編號！');
        }
        //檢查問答題單編碼向度層級是否為空白
        if (empty ($strDimCode)) {
            throw new Exception('缺少問答題單編碼向度層級！');
        }
        $strOQSDCode = $this->nextOQSDCode($intOQSId, $strDimCode);
        $strNow = $this->getNowTime();
        $sql_addOQSD = 'INSERT INTO `OQSDim` (`OQSId`, `DimCode`, `OQSDCode`) VALUES(' . $intOQSId . ', \'' . $strDimCode . '\', \'' . $strOQSDCode . '\')';
        $rs_addOQSD = $this->mysqli->query($sql_addOQSD);
        if (!$rs_addOQSD) {
            throw new Exception('寫入向度資料時發生錯誤！');
        }else {
            $bolRtn = true;
        }
        return $bolRtn;

    }


    public function getOQSCDimLevelNameByOQSId($intOQSId) {
        if(empty ($intOQSId)) {
            throw new Exception('缺少問答單編碼編號！');
        }

        $aryDimLevelName = array();

        $sql_getDimCode = 'SELECT `DimCode` FROM `OQSDim` WHERE `OQSId`=' . $intOQSId;
        $rs_getDimCode = $this->mysqli->query($sql_getDimCode);

        if(!$rs_getDimCode) {
            throw new Exception('查詢問答單編碼向度層級碼時發生錯誤！');
        }else {
        
            while($row_getDimCode = $rs_getDimCode->fetch_row()) {
                $strDimCode = $row_getDimCode[0];
                $intDimId = intval(substr($strDimCode, 0, 2));
                $intLevelId = intval(substr($strDimCode, 2, 2));

                $sql_getDimName = 'SELECT `SDChtName` FROM `SDimension` WHERE `SDId`=' . $intDimId;
                $rs_getDimName = $this->mysqli->query($sql_getDimName);
                $row_getDimName = $rs_getDimName->fetch_row();

                $sql_getLevelName = 'SELECT `SDLChtName` FROM `SDimLevel` WHERE `SDId`=' . $intDimId . ' AND `SDLOrder`=' . $intLevelId;
                $rs_getLevelName = $this->mysqli->query($sql_getLevelName);
                $row_getLevelName = $rs_getLevelName->fetch_row();

                $strDimLevelName = $row_getDimName[0] . '-' . $row_getLevelName[0];

                array_push($aryDimLevelName, $strDimLevelName);

                //釋放查詢結果
                $rs_getDimName->free_result();
                $rs_getLevelName->free_result();

            }   //End While

            $rs_getDimCode->free_result();
        }

        return $aryDimLevelName;

    }

    public function getOQSCDimLevelCodeByOQSId($intOQSId){
        if(empty ($intOQSId)) {
            throw new Exception('缺少問答單編碼編號！');
        }

        $aryDimLevelCode = array();

        $sql_getDimLevelCode = 'SELECT `DimCode` FROM `OQSDim` WHERE `OQSId`=' . $intOQSId . ' ORDER BY `OQSDId`';
        $rs_getDimLevelCode = $this->mysqli->query($sql_getDimLevelCode);

        if(!$rs_getDimLevelCode){
            throw new Exception('查詢問答單編碼向度層級六碼時發生錯誤！');
        }else{
            while ($row_getDimLevelCode = $rs_getDimLevelCode->fetch_row()){
                array_push($aryDimLevelCode, $row_getDimLevelCode[0]);
            }//End while
        }

        return $aryDimLevelCode;
    }

    // $intOQSDId: int
    // Returns Boolean
    public function delOQSCDimByOQSDId($intOQSDId) {
        $bolRtn = false;
        //檢查問答單編碼向度編號是否為空白
        if (empty ($intOQSDId)) {
            throw new Exception('錯誤！缺少向度編號。');
        }

        //刪除
        $sql_delOQSD = 'DELETE FROM `OQSDim` WHERE `OQSDId`=' . $intOQSDId;
        $rs_delOQSD = $this->mysqli->query($sql_delOQSD);
        if (!$rs_delOQSD) {
            throw new Exception('錯誤！刪除向度時發生錯誤。');
        }else {
            $bolRtn = true;
        }

        return $bolRtn;

    }

    public function delOQSCDimByOQSId($intOQSId) {
        $bolRtn = false;
        //檢查解釋範例編號是否為空白
        if (empty ($intOQSId)) {
            throw new Exception('缺少必要參數，無法刪除問答單編碼向度層級資料。');
        }

        //依解釋範例編號刪除向度層級資料
        $sql_delOQSD = 'DELETE FROM `OQSDim` WHERE `OQSId`=' . $intOQSId;
        $rs_delOQSD = $this->mysqli->query($sql_delOQSD);
        if (!$rs_delOQSD) {
            throw new Exception('依解釋範例編號，刪除問答單編碼向度時發生錯誤。');
        }else {
            $bolRtn = true;
        }

        return $bolRtn;

    }

    public function delOQSCDimByOQSIdAndCode($intOQSId,$strDimCode){
        $bolRtn = false;
        if(empty ($intOQSId) || empty ($strDimCode)){
            throw new Exception('缺少必要參數，無法刪除問答單編碼向度階層資料！');
        }

        $sql_delDimLevel = 'DELETE FROM `OQSDim` WHERE `OQSId`=' . $intOQSId . ' AND `DimCode`=\'' . $strDimCode . '\'';
        $rs_delDimLevel = $this->mysqli->query($sql_delDimLevel);
        if(!$rs_delDimLevel){
            throw new Exception('刪除問答單編碼的向度階層發生錯誤！');
        }else{
            $bolRtn = true;
        }

        return $bolRtn;
    }

    public function delOQSCDimBySQId($intSQId) {
        $bolRtn = false;
        //檢查問答單編碼向度編號是否為空白
        if (empty ($intSQId)) {
            throw new Exception('缺少子題編號，無法刪除問答單編碼向度層級資料。');
        }

        //刪除
        $sql_delOQSD = 'DELETE FROM `OQSDim` WHERE `OQSDId` IN (SELECT `OQSId` FROM `OpenQSCode` WHERE `SQId`=' . $intSQId . ')' ;
        $rs_delOQSD = $this->mysqli->query($sql_delOQSD);
        if (!$rs_delOQSD) {
            throw new Exception('依子題編號，刪除問答單編碼向度時發生錯誤。');
        }else {
            $bolRtn = true;
        }

        return $bolRtn;

    }

    // Maybe we do not need this function
    public function getOQSDimAndLevelNameByOQSDId($intOQSDId) {
        if (empty($intOQSDId)) {
            throw new Exception('缺少問答單編碼向度編號，無法查詢向度資料！');
        }

        $aryDAndLName = array();

        $sql_selDimCode = 'SELECT `DimCode` FROM `OQSDim` WHERE `OQSDId`=' . $intOQSDId;
        $rs_selDimCode = $this->mysqli->query($sql_selDimCode);
        if(!$rs_selDimCode) {
            throw new Exception('查詢向度編號資料時發生錯誤！');
        }else {
            while ($row_selDimCode = $rs_selDimCode->fetch_row()) {
                $strDimCode = $row_selDimCode[0];
                $intSDId = intval(substr($strDimCode, 0, 2));
                $intSDLOrder = intval(substr($strDimCode, 2, 2));

                $sql_getDimName = 'SELECT `SDChtName` FROM `SDimension` WHERE `SDId`=' . $intSDId;
                $rs_getDimName = $this->mysqli->query($sql_getDimName);
                $row_getDimName = $rs_getDimName->fetch_row();

                $sql_getLevelName = 'SELECT `SDLChtName` FROM `SDimLevel` WHERE `SDId`=' . $intSDId . ' AND `SDLOrder`=' . $intSDLOrder;
                $rs_getLevelName = $this->mysqli->query($sql_getLevelName);
                $row_getLevelName = $rs_getLevelName->fetch_row();

                $strDimAndLevelName = $row_getDimName[0] . '-' . $row_getLevelName[0];

                array_push($aryDAndLName, $strDimAndLevelName);

                //釋放查詢結果
                $rs_getDimName->free_result();
                $rs_getLevelName->free_result();

            }
            $rs_selDimCode->free_result();
        }

        return $aryDAndLName;

    }
    // $intOQSDId: int
    // Returns Array
    public function getOQSDimInfoByOQSDId($intOQSDId) {
        //檢查問答單編碼向度編號是否為空白
        if (empty ($intOQSDId)) {
            throw new Exception('缺少問答單編碼向度編號！');
        }

        $sql_getOQSDById = 'SELECT * FROM `OQSDim` WHERE `OQSDId`=' . $intOQSDId;
        $rs_getOQSDById = $this->mysqli->query($sql_getOQSDById);

        if (!$rs_getOQSDById) {
            throw new Exception('無法查詢該問答單編碼向度的資料！');
        }else {
            if ($rs_getOQSDById->num_rows == 0) {
                throw new Exception('無此問答單編碼向度編號的資料！');
            }else {
                $row_getOQSDById = $rs_getOQSDById->fetch_row();
                return $row_getOQSDById;
            }
        } //End If

    }

    // Returns Array
    public function getOQSDimList() {
        $sql_getOQSDList = 'SELECT * FROM `OQSDim` ;';
        $rs_getOQSDList = $this->mysqli->query($sql_getOQSDList);

        if (!$rs_getOQSDList) {
            throw new Exception('無法查詢該問答單編碼向度的資料！');
        }else {
            if ($rs_getOQSDList->num_rows == 0) {
                throw new Exception('無此問答單編碼向度編號的資料！');
            }else {
                $aryOQSDList = array();
                while ($aryOneOQSD = $rs_getOQSDList->fetch_row()) {
                    array_push($aryOQSDList, $aryOneOQSD);
                }
                return $aryOQSDList;
            }
        } //End If

    }

    private function getNowTime() {
        return date('Y-m-d H:i:s');
    }

    function  __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
            $this->mysqli = FALSE;
        }
    }
}
?>
